package logic.dao;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import logic.bean.BookBean;
import logic.bean.ReadBook;
import logic.beanenum.BookStatus_Enum;
import logic.constants.Const;
import logic.constants.DefaultConsts;
import logic.db.DBHelper;
import logic.table.BooksTable;
import logic.table.BooksTable.BookColums;
import logic.util.BroadcastUtil;
import logic.util.Util;

import org.geometerplus.fbreader.book.Book;
import org.geometerplus.zlibrary.core.filesystem.ZLFile;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteStatement;


public class BookDao {
    private Context context;
    //    private static BookDao dao;
    private DBHelper dbHelper;
    private final SQLiteDatabase myDatabase;

    private BookDao(Context context) {
        this.context = context;
        dbHelper = DBHelper.getInstance(context);
        myDatabase = dbHelper.getSqliteDatabaseW();
    }

    public static BookDao getInstance(Context context) {
//            dao = new BookDao(context);
        return new BookDao(context);
    }

    /**
     * 添加书籍
     */
    public boolean addBooktoShelf(BookBean book) {
        if (null == book) {
            return false;
        }
        BookBean temp = getBookById(book.book_id, book.account);
        //如果有相同的本地书籍就进行更新
        if (temp != null && book.book_id == -1 && book.book_name.equals(temp.book_name)) {
            book.create_time = String.valueOf(System.currentTimeMillis());
            if (book.chapter_id < temp.chapter_id) {
                book.chapter_id = 0;
            }
            updateShelfBook(book, false);
            //在线书籍
        } else if (temp != null && book.book_id != -1 && temp.book_id == book.book_id) {
            book.create_time = String.valueOf(System.currentTimeMillis());
            if (book.chapter_id < temp.chapter_id) {
                book.chapter_id = 0;
            }
            updateShelfBook(book, false);
            //			if(temp.status == BookStatus_Enum.DOWNLOAD.getValue()){ //如果是已下载的书籍，只更新阅读时间
            //				updateReadTime(book);
            //			}else{
            //				updateShelfBook(book);
            //			}
        } else {
            return add(book);
        }
        return true;
    }

    /**
     * 同步服务端的阅读记录
     */
    public void syncBooks(List<ReadBook> books) {
        List<Long> bookIdList = new ArrayList<>();//服务端书籍id

        if (null == books/* || books.isEmpty()*/) {
            return;
        }
        for (ReadBook readBook : books) {
            bookIdList.add(readBook.bookId);
            BookBean book = readBook.toBookBean();//服务器返回书籍
            BookBean temp = getBookById(book.book_id, book.account);//本地数据库书籍
            if (temp != null && book.book_id != -1 && temp.book_id == book.book_id) {
                if (temp.status == BookStatus_Enum.REFERRAL.getValue()) {//推荐书籍
                    book.status = BookStatus_Enum.ONLINE.getValue();
                    book.isOnline = BookBean.ONLINE;
                } else if (temp.status == BookStatus_Enum.DOWNLOAD.getValue()) {//下载书籍
                    book.status = BookStatus_Enum.DOWNLOAD.getValue();
                    book.isOnline = BookBean.NO_ONLINE;
                } else if (book.isOnline == BookBean.NO_ONLINE && temp.status == BookStatus_Enum.ONLINE.getValue()) {//在线书籍
                    book.status = BookStatus_Enum.ONLINE.getValue();
                    book.isOnline = BookBean.ONLINE;
                }
                updateShelfBook(book, true);
            } else {
                book.isOnline = BookBean.ONLINE;
                add(book);
            }
        }

        //对比网络在线书籍,将已经删除的书籍从数据库中移除
        List<BookBean> myShelfBooks = BookDao.getInstance(context).getShelfBooks(Const.phone_number);//本地书籍
        for (BookBean myShelfBook : myShelfBooks) {
            if (myShelfBook.status == BookStatus_Enum.ONLINE.getValue() && !bookIdList.contains(myShelfBook.book_id)) {
                delReadingBook(myShelfBook);//删除本地书籍
            }
        }
    }

    private boolean add(BookBean book) {
        ContentValues values;
        try {
            values = dbHelper.getContentValues(DBHelper.TYPE_INSERT, book);
            dbHelper.getSqliteDatabaseW().insert(BooksTable.table, null, values);
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            BroadcastUtil.booksDataChange(context, DefaultConsts.ADD, null);
            values = null;
            //dbHelper.closeSqliteDatabaseW();
        }
        return true;
    }

    /** 添加推荐书籍，帐号第一次登录需要调用 */
//	public void addReferralBooks(String account) {
//		ContentValues values = new ContentValues();
//		values.put(BookColums.STATUS, BookStatus_Enum.REFERRAL.getValue());
//		values.put(BookColums.BOOK_ID, "100000000573");
//		values.put(BookColums.BOOK_NAME, "狼图腾");
//		values.put(BookColums.BOOK_TYPE, BookType_Enum.BOOK.getValue());
//		values.put(BookColums.ACCOUNT, account);
//		values.put(BookColums.LOGO_URL, "http://42.121.255.86:6080/group1/M00/8F/22/F_fpQcZLXxMrowNaCo.jpg;http://42.121.255.86:6080/group1/M00/E1/C5/F_5MnqmV0iVad30g2R.ncx");
//		ContentValues values1 = new ContentValues();
//		values1.put(BookColums.STATUS, BookStatus_Enum.REFERRAL.getValue());
//		values1.put(BookColums.BOOK_ID, "100000000060");
//		values1.put(BookColums.BOOK_NAME, "百万宝贝计划");
//		values1.put(BookColums.BOOK_TYPE, BookType_Enum.BOOK.getValue());
//		values1.put(BookColums.ACCOUNT, account);
//		values1.put(BookColums.LOGO_URL, "http://42.121.255.86:6080/group1/M00/40/0E/F_IjK5ZvTBAgehshXp.jpg;http://42.121.255.86:6080/group1/M00/46/36/F_cmFWrq7lf2sIloVa.ncx");
//		
//		ContentValues values2 = new ContentValues();
//		values2.put(BookColums.STATUS, BookStatus_Enum.REFERRAL.getValue());
//		values2.put(BookColums.BOOK_ID, "100000000699");
//		values2.put(BookColums.BOOK_NAME, "明朝那些事儿.第1部，洪武大帝");
//		values2.put(BookColums.BOOK_TYPE, BookType_Enum.BOOK.getValue());
//		values2.put(BookColums.ACCOUNT, account);
//		values2.put(BookColums.LOGO_URL, "http://42.121.255.86:6080/group1/M00/D4/35/F_6HMVxeZTYcPHUd21.jpg;http://42.121.255.86:6080/group1/M00/B2/28/F_M60faWBIEQRhQXaZ.ncx");
//		
//		try
//		{
//			if(null==getBookById(100000000573L, account)) dbHelper.getSqliteDatabaseW().insert(BooksTable.table, null, values);
//			if(null==getBookById(100000000060L, account)) dbHelper.getSqliteDatabaseW().insert(BooksTable.table, null, values1);
//			if(null==getBookById(100000000699L, account)) dbHelper.getSqliteDatabaseW().insert(BooksTable.table, null, values2);
//		}
//		catch (Exception e)
//		{
//			e.printStackTrace();
//		}
//		finally
//		{
//			values = null;
//			values1 = null;
//		}
//	}

    /**
     * 添加推荐书籍
     */
    public void addReferralBooks(List<BookBean> list) {
        for (BookBean bean : list) {
            ContentValues values = new ContentValues();
            values.put(BookColums.STATUS, bean.status);
            values.put(BookColums.BOOK_ID, bean.book_id);
            values.put(BookColums.BOOK_NAME, bean.book_name);
            values.put(BookColums.BOOK_TYPE, bean.book_type);
            values.put(BookColums.ACCOUNT, bean.account);
            values.put(BookColums.LOGO_URL, bean.logo_url);
            values.put(BookColums.NCXURL, bean.ncxUrl);
            dbHelper.getSqliteDatabaseW().insert(BooksTable.table, null, values);
        }
    }

    /**
     * 删除推荐书籍
     */
    public int delReferralBooks(String account) {
        try {
            String whereClause = BookColums.STATUS + " = ? and " + BookColums.ACCOUNT + " = ?";
            String[] whereArgs = new String[]{
                    String.valueOf(BookStatus_Enum.REFERRAL.getValue()),
                    account
            };
            return dbHelper.getSqliteDatabaseW().delete(BooksTable.table, whereClause, whereArgs);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //dbHelper.closeSqliteDatabaseW();
        }
        return -1;
    }

    /**
     * 删除所有推荐书籍
     */
    public int delReferralBooks() {
        try {
            String whereClause = BookColums.STATUS + " = ?";
            String[] whereArgs = new String[]{
                    String.valueOf(BookStatus_Enum.REFERRAL.getValue())
            };
            return dbHelper.getSqliteDatabaseW().delete(BooksTable.table, whereClause, whereArgs);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //dbHelper.closeSqliteDatabaseW();
        }
        return -1;
    }

    /**
     * 删除书架书籍
     */
    public int delReadingBook(BookBean book) {
        if (null == book) return -1;
        try {
            String whereClause = BookColums.BOOK_ID + " = ? and " + BookColums.BOOK_NAME + " = ? and " + BookColums.ACCOUNT + " = ?";
            String[] whereArgs = new String[]{
                    String.valueOf(book.book_id),
                    book.book_name,
                    book.account
            };
            return dbHelper.getSqliteDatabaseW().delete(BooksTable.table, whereClause, whereArgs);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //dbHelper.closeSqliteDatabaseW();
        }
        return -1;
    }

    /**
     * 更新书籍
     *
     * @param book book
     * @param sync true:和服务端同步更新，false:本地更新
     * @return int
     */
    public int updateShelfBook(BookBean book, boolean sync) {
        if (null == book) return -1;
        try {
            String whereClause = BookColums.BOOK_ID + " = ? and " + BookColums.BOOK_NAME + " = ? and " + BookColums.ACCOUNT + " = ?";
            String[] whereArgs = new String[]{String.valueOf(book.book_id), book.book_name, book.account};
            ContentValues values = new ContentValues(); //dbHelper.getContentValues(DBHelper.TYPE_UPDATE, book);
            values.put(BookColums.STATUS, book.status);
            values.put(BookColums.CREATE_TIME, String.valueOf(book.create_time));
            if (Util.isNotEmpty(book.author)) {
                values.put(BookColums.AUTHOR, book.author);
            }
            if (Util.isNotEmpty(book.path)) {
                values.put(BookColums.PATH, book.path);
            }
            if (book.server_chapterid > 0) {
                values.put(BookColums.SERVER_CHAPTERID, book.server_chapterid);
            }
            if (sync) {//同步操作
                if (Util.isNotEmpty(book.ncxUrl)) values.put(BookColums.NCXURL, book.ncxUrl);
                values.put(BookColums.SERVER_ID, book.server_id);
                values.put(BookColums.ISONLINE, book.isOnline);
                values.put(BookColums.SYNCSTATUS, BookBean.SYNC);
            } else {
                if (book.chapter_id > 0) { //大于当前阅读的章节进行更新
                    values.put(BookColums.CHAPTER_ID, book.chapter_id);
                }
                values.put(BookColums.STARTTIME, book.startTime);
                values.put(BookColums.ENDTIME, book.endTime);
                values.put(BookColums.CHAPTER_NAME, book.chapterName);
                values.put(BookColums.ISONLINE, book.status == BookStatus_Enum.DOWNLOAD.getValue() ? BookBean.NO_ONLINE : BookBean.ONLINE);
                values.put(BookColums.SYNCSTATUS, BookBean.NO_SYNC);
            }
            String logoUrl = book.logo_url;
            if (Util.isNotEmpty(logoUrl)) {
                values.put(BookColums.LOGO_URL, logoUrl);
            }

            return dbHelper.getSqliteDatabaseW().update(BooksTable.table, values, whereClause, whereArgs);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            BroadcastUtil.booksDataChange(context, sync ? DefaultConsts.UPDATE_SYNC : DefaultConsts.UPDATE_NO_SYNC, null);
            //dbHelper.closeSqliteDatabaseW();
        }
        return -1;
    }

    /**
     * 更新书籍阅读时间
     */
    public int updateReadTime(BookBean book) {
        if (null == book) return -1;
        try {
            String whereClause = BookColums.BOOK_ID + " = ? and " + BookColums.BOOK_NAME + " = ? and " + BookColums.ACCOUNT + " = ?";
            String[] whereArgs = new String[]{
                    String.valueOf(book.book_id),
                    book.book_name,
                    book.account
            };
            final ContentValues values = new ContentValues();
            values.put(BookColums.CREATE_TIME, String.valueOf(book.create_time));
            return dbHelper.getSqliteDatabaseW().update(BooksTable.table, values, whereClause, whereArgs);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //dbHelper.closeSqliteDatabaseW();
        }
        return -1;
    }

    /**
     * 查询未同步的数据
     */
    public List<BookBean> getSyncBooks(String account) {
        if (Util.isEmpty(account)) return null;
        List<BookBean> books = new ArrayList<>();
        String selection = BookColums.SYNCSTATUS + " <> ? and " + BookColums.ACCOUNT + " = ? and " + BookColums.STATUS + " in (?,?)";
        String[] selectionArgs = new String[]{
                String.valueOf(BookBean.SYNC),
                account,
                String.valueOf(BookStatus_Enum.ONLINE.getValue()),
                String.valueOf(BookStatus_Enum.DOWNLOAD.getValue())
        };
        Cursor c = dbHelper.getSqliteDatabaseR().query(BooksTable.table, null, selection, selectionArgs, null, null, null);
        try {
            if (null != c) {
                while (c.moveToNext()) {
                    BookBean bean = dbHelper.getObject(BookBean.class, c);
                    books.add(bean);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            dbHelper.closeCursor(c);
        }
        return books;
    }

    /**
     * 批量更新为同步状态
     */
    public void updateSyncBooks(String account, List<BookBean> books) {
        if (Util.isEmpty(account) || books == null || books.isEmpty()) return;
        for (BookBean book : books) {
            String whereClause = BookColums.BOOK_ID + " = ? and " + BookColums.BOOK_NAME + " = ? and " + BookColums.ACCOUNT + " = ?";
            String[] whereArgs = new String[]{
                    String.valueOf(book.book_id),
                    book.book_name,
                    book.account
            };
            final ContentValues values = new ContentValues();
            values.put(BookColums.SYNCSTATUS, String.valueOf(BookBean.SYNC));
            dbHelper.getSqliteDatabaseW().update(BooksTable.table, values, whereClause, whereArgs);
        }
    }

    /**
     * 更新同步状态
     */
    public void updateSyncBook(BookBean book) {
        if (book == null) {
            return;
        }
        String whereClause = BookColums.BOOK_ID + " = ? and " + BookColums.BOOK_NAME + " = ? and " + BookColums.ACCOUNT + " = ?";
        String[] whereArgs = new String[]{
                String.valueOf(book.book_id),
                book.book_name,
                book.account
        };
        final ContentValues values = new ContentValues();
        values.put(BookColums.SYNCSTATUS, String.valueOf(book.syncStatus));
        dbHelper.getSqliteDatabaseW().update(BooksTable.table, values, whereClause, whereArgs);
        if (book.syncStatus == BookBean.DELETE) {
            BroadcastUtil.booksDataChange(context, DefaultConsts.DELETE, book);
        }
    }

    /**
     * 取阅读过的书籍
     */
    public List<BookBean> getShelfBooks(String account) {
        if (Util.isEmpty(account)) return null;
        List<BookBean> books = new ArrayList<BookBean>();
        String selection = BookColums.STATUS + " <> ? and " + BookColums.ACCOUNT + " = ? and " + BookColums.SYNCSTATUS + " <> ?";
        String[] selectionArgs = new String[]{
                "4",
                account,
                String.valueOf(BookBean.DELETE)
        };
        String orderBy = BookColums.CREATE_TIME + " DESC";
        Cursor c = dbHelper.getSqliteDatabaseR().query(BooksTable.table, null, selection, selectionArgs, null, null, orderBy);
        try {
            if (null != c) {
                while (c.moveToNext()) {
                    BookBean bean = new BookBean();
                    bean = dbHelper.getObject(BookBean.class, c);
                    books.add(bean);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            dbHelper.closeCursor(c);
            //dbHelper.closeSqliteDatabaseR();
        }
        return books;
    }

    /**
     * 根据bookid查询对象
     */
    public BookBean getBookById(long contentId, String account) {
        BookBean bean = null;
        String selection = BookColums.BOOK_ID + " = ? AND " + BookColums.ACCOUNT + " = ?";
        String[] selectionArgs = new String[]{
                String.valueOf(contentId),
                account
        };
        Cursor c = dbHelper.getSqliteDatabaseR().query(BooksTable.table, null, selection, selectionArgs, null, null, null);
        try {
            if (null != c && c.getCount() > 0) {
                c.moveToFirst();
                bean = dbHelper.getObject(BookBean.class, c);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            dbHelper.closeCursor(c);
            //dbHelper.closeSqliteDatabaseR();
        }
        return bean;
    }

    public String getDbIdByContentId(String contentId, String account) {
        String dbId = null;
        String[] cloumns = new String[]{
                BookColums.ID
        };
        String selection = BookColums.BOOK_ID + " = ? AND " + BookColums.ACCOUNT + " = ?";
        String[] selectionArgs = new String[]{
                contentId,
                account
        };
        Cursor c = dbHelper.getSqliteDatabaseR().query(BooksTable.table, cloumns, selection, selectionArgs, null, null, null);
        try {
            if (null != c && c.moveToFirst()) {
                dbId = c.getString(0);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            dbHelper.closeCursor(c);
        }
        return dbId;
    }

    /**
     * 获取同步最近阅读的增量id
     */
    public long getMaxServerId(String account) {
        String[] selectionArgs = new String[]{
                account,
                String.valueOf(BookBean.DELETE)
        };
        long id = 0L;
        Cursor c = dbHelper.getSqliteDatabaseR().rawQuery("SELECT MAX(" + BookColums.SERVER_ID + ") FROM " + BooksTable.table + " where " + BookColums.ACCOUNT + " = ? and " + BookColums.SYNCSTATUS + " <> + ?", selectionArgs);
        try {
            if (null != c) {
                c.moveToFirst();
                id = c.getLong(0);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            dbHelper.closeCursor(c);
        }
        return id;
    }

    public BookBean getBookById(long id, long contentId, String account) {
        BookBean bean = null;
        String selection = BookColums.ID + " = ? AND " + BookColums.BOOK_ID + " = ? AND " + BookColums.ACCOUNT + " =? ";
        String[] selectionArgs = new String[]{
                String.valueOf(id),
                String.valueOf(contentId),
                account
        };
        Cursor c = dbHelper.getSqliteDatabaseR().query(BooksTable.table, null, selection, selectionArgs, null, null, null);
        try {
            if (null != c) {
                c.moveToFirst();
                bean = dbHelper.getObject(BookBean.class, c);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            dbHelper.closeCursor(c);
            //dbHelper.closeSqliteDatabaseR();
        }
        return bean;
    }

    /**
     * 取推荐的书籍
     */
    public List<BookBean> getReferralBooks(String account) {
        List<BookBean> books = new ArrayList<BookBean>();

        String selection = BookColums.STATUS + " = ? and " + BookColums.ACCOUNT + " = ?";
        String[] selectionArgs = new String[]{String.valueOf(BookStatus_Enum.REFERRAL.getValue()), account};
        Cursor c = dbHelper.getSqliteDatabaseR().query(BooksTable.table, null, selection, selectionArgs, null, null, null);
        try {
            if (null != c) {
                while (c.moveToNext()) {
                    BookBean bean = new BookBean();
                    bean = dbHelper.getObject(BookBean.class, c);
                    books.add(bean);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            dbHelper.closeCursor(c);
            //dbHelper.closeSqliteDatabaseR();
        }
        return books;
    }

    public Book loadBook(long bookId) {
        Book book = null;
        final Cursor cursor = myDatabase.rawQuery("SELECT book_name,book_id,chapter_id, path FROM Books WHERE id = " + bookId, null);
        if (cursor.moveToNext()) {
            ZLFile file = ZLFile.createFileByPath(cursor.getString(3));
            book = createBook(bookId, file, cursor.getString(0), "", "", cursor.getString(1), cursor.getString(2));
        }
        cursor.close();
        return book;
    }

    public List<Book> loadMyBooks(String contentId) {
        ArrayList<Book> books = new ArrayList<>();
        Book book;

        final Cursor cursor = myDatabase.rawQuery("SELECT book_name,book_id,chapter_id, path, id  FROM Books WHERE book_id = " + contentId, null);
        if (cursor.moveToNext()) {
            ZLFile file = ZLFile.createFileByPath(cursor.getString(3));
            book = createBook(cursor.getLong(4), file, cursor.getString(0), "", "", cursor.getString(1), cursor.getString(2));
            books.add(book);
        }
        cursor.close();
        return books;
    }

    public Book loadBookByFile(ZLFile file) {
        Book book = null;
        String path = file.getPath();
        String sqlStr = "SELECT book_name,book_id,chapter_id, id FROM Books WHERE path = '" + path + "'";
        final Cursor cursor = myDatabase.rawQuery(sqlStr, null);
        if (cursor.moveToNext()) {
            book = createBook(cursor.getLong(3), file, cursor.getString(0), "", "", cursor.getString(1), cursor.getString(2));
        }
        cursor.close();
        return book;
    }

    public Map<Long, Book> loadBooks() {
        final HashMap<Long, Book> booksById = new HashMap<>();
        Cursor cursor = myDatabase.rawQuery("SELECT book_name,book_id,chapter_id, path, id  FROM Books ", null);
        while (cursor.moveToNext()) {
            final long id = cursor.getLong(4);
            ZLFile file = ZLFile.createFileByPath(cursor.getString(3));
            Book book = createBook(cursor.getLong(4), file, cursor.getString(0), "", "", cursor.getString(1), cursor.getString(2));
            if (book != null) {
                booksById.put(id, book);
            }
        }
        cursor.close();
        cursor = myDatabase.rawQuery("SELECT book_id FROM Bookmarks WHERE visible = 1 GROUP by book_id", null);
        while (cursor.moveToNext()) {
            final Book book = booksById.get(cursor.getLong(0));
            if (book != null) {
                book.HasBookmark = true;
            }
        }
        cursor.close();
        return booksById;
    }

    private SQLiteStatement myUpdateBookInfoStatement;

    public void updateBookInfo(long bookId, String path, String title) {
        if (myUpdateBookInfoStatement == null) {
            myUpdateBookInfoStatement = myDatabase.compileStatement("UPDATE OR IGNORE Books SET path = ?,  book_name = ? WHERE id = ?");
        }
        myUpdateBookInfoStatement.bindString(1, path);
        myUpdateBookInfoStatement.bindString(2, title);
        myUpdateBookInfoStatement.bindLong(3, bookId);
        myUpdateBookInfoStatement.execute();
    }

    private SQLiteStatement myUpdateBookContentIdStatement;

    public void saveBookContentId(long bookId, String contentId, String chapterId) {
        System.out.println("contentId:" + contentId + "||chapterId:" + chapterId);
        myUpdateBookContentIdStatement = myDatabase.compileStatement("UPDATE OR IGNORE Books SET book_id = " + contentId + " , chapter_id = " + chapterId + " WHERE id = ?");
        myUpdateBookContentIdStatement.bindLong(1, bookId);
        myUpdateBookContentIdStatement.execute();
    }

    protected Book createBook(long id, ZLFile file, String title, String encoding, String language, String contentId, String chapterId) {
        return file != null ? new Book(id, file, title, encoding, language, contentId, chapterId) : null;
    }
}
